<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*,java.util.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html>
<head>
<title>薪金明细</title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
<script type="text/javascript">
	function toexcel(){
		window.location.href="salaryDetail.jsp?data=<%=request.getParameter("data")%>";
	}
</script>
</head>
<body bgcolor="#CCFFCC">
<%
	String icode = request.getParameter("data");
	DataSetTool DST = new DataSetTool();

	List<String> itemCode = new ArrayList<String>();  //记录所有要显示的工资项目，对A20和A8（社保）开头的做特别处理
	List<String> salaryItem = new ArrayList<String>();  //记录A01-A09，看看具体有多少工资项目要计算为薪金的
	List<String> socialItem = new ArrayList<String>();  //记录社保的薪金项目

	Map<String, String> item = new HashMap<String, String>();  //保存薪金的键-值对
	Map<String, Double> divisionSum = new HashMap<String, Double>();  //保存所有的小部门的数据汇总
	Map<String, Double> departmentSum = new HashMap<String, Double>();  //保存所有的大部门的数据汇总
	Map<String, Double> allSum = new HashMap<String, Double>();  //保存该发薪记录的所有薪金汇总

	String sumSalaryItem = "A01A05A77D11D14D17D20D21D22D23D24";

	//分别初始化3个汇总中的帮工工资、总工资、总收入和实际收入的值
	divisionSum.put("A20", 0.0);
	departmentSum.put("A20", 0.0);
	allSum.put("A20", 0.0);
	divisionSum.put("A00", 0.0);
	departmentSum.put("A00", 0.0);
	allSum.put("A00", 0.0);
	divisionSum.put("sum", 0.0);
	departmentSum.put("sum", 0.0);
	allSum.put("sum", 0.0);
	divisionSum.put("pay", 0.0);
	departmentSum.put("pay", 0.0);
	allSum.put("pay", 0.0);

	String department = "";
	String division = "";
	int divisionCount = 0;
	int departmentCount = 0;
	int allCount = 0;
	String itemSql = "select code,desc_cn from hr_salary_item where sort != 0 order by sort"; //只要勾选了就在此报表中显示,并且
	DataSet itemRs = DST.getDataSet(itemSql);
	int row = itemRs.getRowCount();

	for (int i = 0; i < row; i++) {
		String code = itemRs.getValue(i, "code");
		String name = itemRs.getValue(i, "desc_cn");

		if (code.equals("A20"))
			continue; //帮工工资不在算在薪金排序里，固定放在总收入和实际收入之间

		//判断如果为A8开头的薪金项目，全计算统计到雇员供款中，默认的供款的大小部门汇总（保存在MAP中）
		//已经设置，所以这里不需要设置，只需要设置其它薪金项目的就行（ELSE条件里的）
		if (code.indexOf("A8") != -1) {
			if (item.get("A80") == null || item.get("A80").equals(""))
				item.put("A80", "雇员供款");
			if (itemCode.size() == 0 || !itemCode.get(itemCode.size() - 1).equals("A80")) {
				itemCode.add("A80");
				divisionSum.put("A80", 0.0);
				departmentSum.put("A80", 0.0);
				allSum.put("A80", 0.0);
			}
		} else {
			item.put(code, name);
			divisionSum.put(code, 0.0);
			departmentSum.put(code, 0.0);
			allSum.put(code, 0.0);
			itemCode.add(code);
		}

		//保存所有社保薪金项和工资项
		if (code.indexOf("A0") != -1)
			salaryItem.add(code);
		if (code.indexOf("A8") != -1)
			socialItem.add(code);
	}

	String salarySql = "select * from (select case when afterDepName.before is null then case when beforeDepName.systemname is null then dep_name.systemname else beforeDepName.systemname end else afterDepName.beforeSystemName end systemname"
		+ ",case when afterDepName.before is null then case when beforeDepName.name is null then dep_name.name else beforeDepName.name end else afterDepName.before end name,dep_name.employno as bcode,dep_name.bname as bname, isnull(A20.amount, 0) as A20";
	String leftSql = "";

	//这个FOR循环用来拼接LEFT JOIN语句和拼接查询结果，并对排序为第一的薪金项目的LEFT JOIN语句特别处理，
	//如果项目为A80（社保的）不进行拼接，在后面循环记录所有的社保项目中处理
	for (String code : itemCode) {
		if (!code.equals("A80")) {
			salarySql += ",isnull(" + code + ".amount, 0) as " + code;
			
			leftSql += "left join "
				+ "(select sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='"
				+ code + "' group by bcode,upcode) as " + code
				+ " on " + code + ".bcode = main.bcode and " + code + ".upcode = main.upcode ";
		}
	}
	salarySql += ",";

	//拼接所有的工资项汇总
	for (String salaryCode : salaryItem) {
		salarySql += " isnull(" + salaryCode + ".amount, 0) +";
	}
	salarySql = salarySql.substring(0, salarySql.length() - 1) + "as A00 ,";

	//拼接社保汇总，并且查询出所有的社保的LEFT语句
	for (String socialCode : socialItem) {
		salarySql += " isnull(" + socialCode + ".amount, 0) +";
		leftSql += "left join "
				+ "(select sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='"
				+ socialCode + "' group by bcode,upcode) as "
				+ socialCode + " on " + socialCode + ".bcode = main.bcode and " + socialCode
				+ ".upcode = main.upcode ";
	}
	
	//这里是一个特别处理，当有社保项的时候和没有的时候的处理
	if (socialItem.size() > 0) {
		salarySql = salarySql.substring(0, salarySql.length() - 1) + "as A80 ";
	} else {
		salarySql = salarySql.substring(0, salarySql.length() - 1);
	}

	salarySql += "from (select upcode,Start_Time,End_Time,bcode from hr_salary_main_c, (select upcode, bcode from hr_salary group by upcode, bcode) as hr_salary "
			+ "where hr_salary.upcode = hr_salary_main_c.code and hr_salary_main_c.icode = '"
			+ icode
			+ "') as main "
			+ leftSql
			+ "left join "
			+ "(select sum(cast(countje as money)) as amount,bcode from hr_salary_helper,hr_salary_main_c where hr_salary_main_c.icode='"
			+ icode
			+ "' and helper_date >= start_time and helper_date <= end_time group by bcode) as A20 "
			+ "on A20.bcode = main.bcode "
			+ "inner join "
			+ "(select systeminfo.name, employno, (select name from systeminfo where id = left(bcode.systemid,4)) as systemname,bcode.bcode,bcode.bname from bcode,bcode_job,systeminfo "
			+ "where systeminfo.id = bcode.systemid and bcode.bcode = bcode_job.bcode) as dep_name "
			+ "on dep_name.bcode = main.bcode "
			+ "left join "
			+ "(select top 1 bcode, after as name, (select name from systeminfo where id = left((select id from systeminfo where name = after),4)) as systemname "
			+ "from hr_EmployeeChange where icode in ( "
			+ "select max(hr_EmployeeChange.icode) "
			+ "from hr_EmployeeChange, hr_salary_main_c "
			+ "where changetype = '部门' and availableDate <= end_time and hr_salary_main_c.icode = '"
			+ icode
			+ "' group by bcode)) as beforeDepName "
			+ "on beforeDepName.bcode = main.bcode "
			+ "left join "
			+ "(select top 1 bcode, before, (select name from systeminfo where id = left((select id from systeminfo where name = before),4)) as beforeSystemName "
			+ "from hr_EmployeeChange where icode in ( "
			+ "select min(hr_EmployeeChange.icode) "
			+ "from hr_EmployeeChange, hr_salary_main_c "
			+ "where changetype = '部门' and availableDate > end_time and hr_salary_main_c.icode = '"
			+ icode
			+ "' group by bcode)) as afterDepName "
			+ "on afterDepName.bcode = main.bcode) a "
			+ "order by systemname,name,bcode";

	/**
	String salarySql1 = "select dep_name.systemname,dep_name.name,job.employno as bcode,dep_name.bname as bname,A01.amount as A01,A05.amount as A05,D20.amount as D20,"
			+ "D11.amount as D11,A20.amount as A20,D12.amount as D12,D14.amount as D14,D17.amount as D17,D01.amount as D01,D04.amount as D04,"
			+ "D06.amount as D06,D02.amount as D02,D18.amount as D18,A83.amount + A80.amount + A81.amount + A82.amount as A83, D21.amount as D21, A99.amount as A99 "
			+ "from (select top 1 code,Start_Time,End_Time from hr_salary_main_c "
			+ "where icode = '"
			+ icode
			+ "') as main "
			+ "left join "
			+ "(select sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A01' group by bcode,upcode) as A01 "
			+ "on main.code = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A05' group by bcode,upcode) as A05 "
			+ "on A05.bcode = A01.bcode and A05.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D20' group by bcode,upcode) as D20 "
			+ "on D20.bcode = A01.bcode and D20.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D11' group by bcode,upcode) as D11 "
			+ "on D11.bcode = A01.bcode and D11.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A20' group by bcode,upcode) as A20 "
			+ "on A20.bcode = A01.bcode and A20.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D12' group by bcode,upcode) as D12 "
			+ "on D12.bcode = A01.bcode and D12.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D14' group by bcode,upcode) as D14 "
			+ "on D14.bcode = A01.bcode and D14.upcode = A01.upcode "
			+ "left join  "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D17' group by bcode,upcode) as D17 "
			+ "on D17.bcode = A01.bcode and D17.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D01' group by bcode,upcode) as D01 "
			+ "on D01.bcode = A01.bcode and D01.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D04' group by bcode,upcode) as D04 "
			+ "on D04.bcode = A01.bcode and D04.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D06' group by bcode,upcode) as D06 "
			+ "on D06.bcode = A01.bcode and D06.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D02' group by bcode,upcode) as D02 "
			+ "on D02.bcode = A01.bcode and D02.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D18' group by bcode,upcode) as D18 "
			+ "on D18.bcode = A01.bcode and D18.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A80' group by bcode,upcode) as A80 "
			+ "on A80.bcode = A01.bcode and A80.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A81' group by bcode,upcode) as A81 "
			+ "on A81.bcode = A01.bcode and A81.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A82' group by bcode,upcode) as A82 "
			+ "on A82.bcode = A01.bcode and A82.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A83' group by bcode,upcode) as A83 "
			+ "on A83.bcode = A01.bcode and A83.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='A99' group by bcode,upcode) as A99 "
			+ "on A99.bcode = A01.bcode and A99.upcode = A01.upcode "
			+ "left join "
			+ "(select  sum(amount) as amount,bcode,upcode from hr_salary where allowanceno_='D21' group by bcode,upcode) as D21 "
			+ "on D21.bcode = A01.bcode and D21.upcode = A01.upcode "
			+ "inner join "
			+ "(select employno,bcode from bcode_job where employstatus = '在职' "
			+ ") as job on job.bcode = A01.bcode "
			+ "inner join "
			+ "(select systeminfo.name,(select name from systeminfo where id = left(bcode.systemid,4)) as systemname,bcode.bcode,bcode.bname from bcode,bcode_job,systeminfo "
			+ "where systeminfo.id = bcode.systemid and bcode.bcode = bcode_job.bcode and bcode_job.employstatus = '在职') as dep_name "
			+ "on dep_name.bcode = A01.bcode "
			+ "order by dep_name.systemname,dep_name.name,A01.bcode";
	 */
	DataSet salaryRs = DST.getDataSet(salarySql);
	System.out.println("salarySql-=====" + salarySql);
%>
<div>
<input type="button" onclick="toexcel();" value="导出到EXCEL">
<table id="tableExcel" style="border-collapse: collapse" width="100%" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
    <tr  bgcolor="#e8e8e0" align="center">
      <td>部门</td>
      <td>分部</td>
      <td>雇员编号</td>
      <td>雇员姓名</td>
      <td>工资</td>
      <%
      	for (String code : itemCode) {
      %>
      <td><%=item.get(code)%></td>
      <%
      	}
      %>
      <td>总收入</td>
      <td>帮工工资</td>
      <td>净付</td>
    </tr>
	<%
		for (int i = 0; i < salaryRs.getRowCount(); i++) {
			String newDepartment = salaryRs.getValue(i, "systemname");
			String newDivision = salaryRs.getValue(i, "name");
			divisionCount ++;

			double a20 = Double.parseDouble(salaryRs.getValue(i, "A20"));
			double a00 = Double.parseDouble(salaryRs.getValue(i, "A00"));
			double sum = 0; //总收入
			double pay = 0; //净付

			for (String code : itemCode) {
				double money = Double.parseDouble(salaryRs
						.getValue(i, code));
				divisionSum.put(code, divisionSum.get(code) + money);
				pay += money;
				if (sumSalaryItem.indexOf(code) >= 0) {
					sum += money;
				}
			}
			divisionSum.put("A00", divisionSum.get("A00") + a00);
			divisionSum.put("A20", divisionSum.get("A20") + a20);
			divisionSum.put("sum", divisionSum.get("sum") + sum);
			divisionSum.put("pay", divisionSum.get("pay") + pay);

			if (!department.equals(newDepartment)) {
				department = newDepartment;
	%>
	<tr>
		<td><%=department%></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<%
    		for (String code : itemCode) {
    			String money = salaryRs.getValue(i, code);
    	%>
    		<td></td>
		<%
			}
		%>
		<td></td>
		<td></td>
		<td></td>
	</tr>
	<%
		}
			if (!division.equals(newDivision)) {
				division = newDivision;
	%>
	<tr>
		<td></td>
		<td><%=division%></td>
		<td></td>
		<td></td>
		<td></td>
		<%
    		for (String code : itemCode) {
    			String money = salaryRs.getValue(i, code);
    	%>
    		<td></td>
		<%
			}
		%>
		<td></td>
		<td></td>
		<td></td>
	</tr>
	<%
		}
	%>
	<tr>
    		<td></td>
    		<td></td>
    		<td><%=salaryRs.getValue(i, "bcode")%></td>
    		<td><%=salaryRs.getValue(i, "bname")%></td>
    		<td><%=RPTString2Double.double2String(a00)%></td>
    		<%
    			for (String code : itemCode) {
    					String money = salaryRs.getValue(i, code);
    		%>
    		<td><%=RPTString2Double.string2String(money)%></td>
			<%
				}
			%>
			<td><%=RPTString2Double.double2String(sum)%></td>
			<td bgcolor="FFFF99"><%=RPTString2Double.double2String(a20)%></td>
			<td><%=RPTString2Double.double2String(pay)%></td>
    	</tr>
	<%
		if ((i + 1) == salaryRs.getRowCount()
					|| !division.equals(salaryRs.getValue(i + 1, "name"))) {
	%>
	<tr>
		<td height="18"></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>	
		<%
    		for (String code : itemCode) {
    			String money = salaryRs.getValue(i, code);
    	%>
    		<td></td>
		<%
			}
		%>
		<td></td>
		<td></td>
		<td></td>
	</tr>
	<tr bgcolor="#cccc77">
		<td></td>
		<td><%=division%>:<%=divisionCount%></td>
		<td></td>
		<td></td>
		<td><%=RPTString2Double.double2String(divisionSum
							.get("A00"))%></td>
		<%
			for (String code : itemCode) {
		%>
		<td><%=RPTString2Double.double2String(divisionSum
				.get(code))%></td>
		<%
			}
		%>
		<td><%=RPTString2Double.double2String(divisionSum
							.get("sum"))%></td>
		<td><%=RPTString2Double.double2String(divisionSum
							.get("A20"))%></td>
		<td><%=RPTString2Double.double2String(divisionSum
							.get("pay"))%></td>
	</tr>
	<%
			departmentCount += divisionCount;
			divisionCount = 0;
			
			for (String code : itemCode) {
				departmentSum.put(code, departmentSum.get(code) + divisionSum.get(code));
				divisionSum.put(code, 0.0);
			}
			departmentSum.put("A00", departmentSum.get("A00") + divisionSum.get("A00"));
			departmentSum.put("A20", departmentSum.get("A20") + divisionSum.get("A20"));
			departmentSum.put("sum", departmentSum.get("sum") + divisionSum.get("sum"));
			departmentSum.put("pay", departmentSum.get("pay") + divisionSum.get("pay"));
			
			divisionSum.put("A00", 0.0);
			divisionSum.put("A20", 0.0);
			divisionSum.put("sum", 0.0);
			divisionSum.put("pay", 0.0);
		}
			if ((i + 1) == salaryRs.getRowCount()
					|| !department.equals(salaryRs.getValue(i + 1,
							"systemname"))) {
	%>
	<tr bgcolor="#cccc77">
		<td><%=department%>:<%=departmentCount%></td>
		<td></td>
		<td></td>
		<td></td>
		<td><%=RPTString2Double.double2String(departmentSum.get("A00"))%></td>
		<%
			for (String code : itemCode) {
		%>
		<td><%=RPTString2Double.double2String(departmentSum
				.get(code))%></td>
		<%
			}
		%>
		<td><%=RPTString2Double.double2String(departmentSum.get("sum"))%></td>
		<td><%=RPTString2Double.double2String(departmentSum.get("A20"))%></td>
		<td><%=RPTString2Double.double2String(departmentSum.get("pay"))%></td>
	</tr>
	<tr>
		<td height="18"></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<%
    		for (String code : itemCode) {
    			String money = salaryRs.getValue(i, code);
    	%>
    		<td></td>
		<%
			}
		%>
		<td></td>
		<td></td>
		<td></td>
	</tr>
	<%
		allCount += departmentCount;
		departmentCount = 0;
		
		for (String code : itemCode) {
			allSum.put(code, allSum.get(code) + departmentSum.get(code));
			departmentSum.put(code, 0.0);
		}
		allSum.put("A00", departmentSum.get("A00") + allSum.get("A00"));
		allSum.put("A20", departmentSum.get("A20") + allSum.get("A20"));
		allSum.put("sum", departmentSum.get("sum") + allSum.get("sum"));
		allSum.put("pay", departmentSum.get("pay") + allSum.get("pay"));
		
		departmentSum.put("A00", 0.0);
		departmentSum.put("A20", 0.0);
		departmentSum.put("sum", 0.0);
		departmentSum.put("pay", 0.0);
			}
		}
	%>
	
	<tr>
		<td>总数：</td>
		<td><%=allCount%></td>
		<td></td>
		<td></td>
		<td><%=RPTString2Double.double2String(allSum.get("A00"))%></td>
		<%
			for (String code : itemCode) {
		%>
		<td><%=RPTString2Double.double2String(allSum
				.get(code))%></td>
		<%
			}
		%>
		<td><%=RPTString2Double.double2String(allSum.get("sum"))%></td>
		<td><%=RPTString2Double.double2String(allSum.get("A20"))%></td>
		<td><%=RPTString2Double.double2String(allSum.get("pay"))%></td>
	</tr>
</table>
</div>
</body>
</html>
